-- 简化版音乐数据库表 DDL 语句 (PostgreSQL版本)

-- 创建音乐表
CREATE TABLE music (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    artist VARCHAR(255) NOT NULL,
    album VARCHAR(255),
    duration INTEGER,
    genre VARCHAR(100),
    release_date DATE,
    file_path VARCHAR(500),
    file_size BIGINT,
    file_format VARCHAR(20),
    cover_image_url VARCHAR(500),
    lyrics TEXT,
    is_favorite BOOLEAN DEFAULT FALSE,
    play_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引
CREATE UNIQUE INDEX idx_music_file_path ON music(file_path);
CREATE INDEX idx_music_title ON music(title);
CREATE INDEX idx_music_artist ON music(artist);
CREATE INDEX idx_music_album ON music(album);
CREATE INDEX idx_music_genre ON music(genre);
CREATE INDEX idx_music_release_date ON music(release_date);

-- 创建更新时间自动更新函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 创建触发器
CREATE TRIGGER update_music_updated_at BEFORE UPDATE
ON music FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 插入示例数据
-- INSERT INTO music (title, artist, album, duration, genre, release_date, file_path, file_size, file_format, cover_image_url) VALUES
-- ('我怀念的', '孙燕姿', '逆光', 289, '流行', '2007-03-22', '/music/孙燕姿/逆光/我怀念的.mp3', 10240000, 'mp3', '/covers/我怀念的.jpg'),
-- ('电灯胆', '邓丽欣', 'Dating Stephy', 236, '流行', '2007-07-01', '/music/邓丽欣/Dating Stephy/电灯胆.mp3', 8960000, 'mp3', '/covers/电灯胆.jpg'),
-- ('笔记', '周笔畅', '谁动了我的琴弦', 245, '流行', '2006-08-15', '/music/周笔畅/谁动了我的琴弦/笔记.mp3', 9216000, 'mp3', '/covers/笔记.jpg');

-- 查询示例
-- 1. 查询所有音乐
-- SELECT * FROM music;

-- 2. 根据艺术家查询音乐
-- SELECT * FROM music WHERE artist = '孙燕姿';

-- 3. 根据专辑查询音乐
-- SELECT * FROM music WHERE album = '逆光';

-- 4. 查询特定类型的音乐
-- SELECT * FROM music WHERE genre = '流行';

-- 5. 查询最近添加的音乐
-- SELECT * FROM music ORDER BY created_at DESC LIMIT 10;

-- 6. 查询播放次数最多的音乐
-- SELECT * FROM music ORDER BY play_count DESC LIMIT 10;

-- 7. 查询收藏的音乐
-- SELECT * FROM music WHERE is_favorite = TRUE;